package com.longyiqun.cm;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
@Component
public class SSh_Connect {
    private int localPort = 3957;
    private int sit_SSHPort = 22;
    private String sit_SSHHostname= "139.196.72.249";
    private String sit_SSHUserName = "testdb";
    private String sit_SSHPassWord = "test123";
    private String sit_HostName = "rm-uf6wv18zh100n05ia.mysql.rds.aliyuncs.com";
    private String sit_UserName = "dbtest";
    private String sit_Password = "Test@Woda2018";
    private int sit_Port = 3306;

    public String sitConnect(String sql,String env) throws ClassNotFoundException, SQLException {
        String[] script = sql.split(";");
        String url  = "";
        if (env.indexOf("sit") != -1){
            url = "jdbc:mysql://localhost:3957/woda_sit?verifyServerCertificate=false&useSSL=true&useUnicode=true&characterEncoding=UTF-8";
        }
        try {
            //连接ssh服务器
            JSch jSch = new  JSch();
            Session session = jSch.getSession(sit_SSHUserName,sit_SSHHostname,sit_SSHPort);
            session.setPassword(sit_SSHPassWord);
            Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            session.setConfig(config);
            session.connect();
            System.out.println(session.getServerVersion());
            //连接数据库
            session.setPortForwardingL("127.0.0.1",localPort,sit_HostName,sit_Port);


            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = null;
            conn = DriverManager.getConnection(url,sit_UserName,sit_Password);
            System.out.println(conn+";;;");
            Statement statement = null;
            statement = conn.createStatement();
            for (int i=0;i<script.length;i++){
                statement.addBatch(script[i]);
            }
            int[] result =  statement.executeBatch();
            System.out.println(result);
            statement.close();
            for (int i = 0; i < script.length; i++) {
                if (result[i] >= 0) {
                    System.out.println("语句: " + script[i] + " 执行成功，影响了"
                            + result[i] + "行数据");
                } else if (result[i] == Statement.SUCCESS_NO_INFO) {
                    System.out.println("语句: " + script[i] + " 执行成功，影响的行数未知");
                } else if (result[i] == Statement.EXECUTE_FAILED) {
                    System.out.println("语句: " + script[i] + " 执行失败");
                    return script[i];
                }
            }

        }catch (Exception e){
            e.printStackTrace();
        }

        return "true";
    }
    public static void clean() throws SQLException,ClassNotFoundException{
        String ss = null;
        String ds = null;
        SSh_Connect sSh_connect = new SSh_Connect();
        String result = sSh_connect.sitConnect(ss,ds);
    }

}
